import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class StationManager {
	public static final String STATION_TABLE = "BUS_STATION";
	public static final String LINE_STATION_TABLE = "LINE_STATION";
	public static final String STATION_ID_COLUMN = "station_id";
	public static final String ZONE_ID_COLUMN = "zone_id";
	public static final String NAME_COLUMN = "address";
	
	
	private static StationManager instance = null;
	public static StationManager getInstance() throws SQLException {
		if (instance == null) {
			instance = new StationManager();
			return instance;
		} else {
			return instance;
		}
	}
	protected StationManager() throws SQLException {
		conn = Database.getAccessDBConnection();
	}
	Connection conn = null;
	
	public Station getStation(int stationId) {
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("SELECT * FROM " + STATION_TABLE 
					+ " WHERE "+ STATION_ID_COLUMN + "=" + stationId);
			ResultSet rs = stmt.getResultSet();
			if (!rs.next()) {
				stmt.close();
				return null;
			} else {
				Station s = new Station(rs);
				stmt.close();
				return s;
			}
		} catch (SQLException e) {
			return null;
		}
	}
	
	public ArrayList<Station> getStationInZone(int zoneId) {
		ArrayList<Station> stations = new ArrayList<Station>();
		try {
			Statement stmt = conn.createStatement();
			boolean a = stmt.execute("SELECT * FROM " + STATION_TABLE + " WHERE "
					+ ZONE_ID_COLUMN + "=" + zoneId + " ORDER BY " + NAME_COLUMN);
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Station s = new Station(rs);
				stations.add(s);
			}
			stmt.close();
		} catch (Exception e) {
		}
		return stations;
	}
	
	public ArrayList<Station> getStationInLine(int lineId) {
		ArrayList<Station> stations = new ArrayList<Station>();
		try {
			Statement stmt = conn.createStatement();
/*			String sql = "SELECT * FROM " + STATION_TABLE + "," + LINE_STATION_TABLE
					+ " WHERE " + STATION_TABLE + ".station_id=" + LINE_STATION_TABLE + ".station_id"
					+ " AND " + LINE_STATION_TABLE + ".line_id=" + lineId;// + " ORDER BY " + LINE_STATION_TABLE + ".order DESC";
			*/
			String sql = "SELECT * FROM view_Station_LineStation WHERE line_id=" + lineId;
			boolean a = stmt.execute(sql);
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Station s = new Station(rs);
				stations.add(s);
			}
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return stations;
	}
	
	public ArrayList<Station> getAllStation() {
		ArrayList<Station> stations = new ArrayList<Station>();
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("SELECT * FROM " + STATION_TABLE
					+ " ORDER BY " + NAME_COLUMN);
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Station s = new Station(rs);
				stations.add(s);
			}
			stmt.close();
		} catch (Exception e) {
		}
		return stations;
	}
	
	public boolean addStation(Station station, int zoneId) {
		try {
			Statement stmt = conn.createStatement();
			String sql = "INSERT INTO " + STATION_TABLE + "(" + NAME_COLUMN + "," + ZONE_ID_COLUMN+ ")"
			+ " VALUES " + "('" + station.getName() + "'," + zoneId + ")"; 
				stmt.executeUpdate(sql);
			stmt.close();
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	public boolean updateStation(int stationId, Station newStation, int newZone) {
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE " + STATION_TABLE
					+ " SET address='" + newStation.getName()
					+ "',zone_id=" + newZone
					+ " WHERE " + STATION_ID_COLUMN + "=" + stationId);
			stmt.close();
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	public boolean removeBusLine(int id) {
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM " + STATION_TABLE + " WHERE " + STATION_ID_COLUMN + "=" + id);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
}
